<?php

Class moderator_model extends CI_Model {

    public function __construct() {
        $this->load->database();
    }

    public function getModerator($userid) {
        $this->db->select('mp.*,u.*');
        $this->db->from('moderator_profile mp, user u');
        $this->db->where('u.user_id', $userid);
        $this->db->where('u.user_id = mp.user_id');
        $query = $this->db->get();
        return $query->row_array();
    }

    public function pieChart($secid,$secname) {
        $this->db->select('level_number,count(*) as number ');
        $this->db->from('student_profile sp,user u');
        $this->db->where('sp.section_id', $secid);
        $this->db->where('sp.user_id = u.user_id');
        $this->db->where('u.isDeleted', 'N');
        $this->db->group_by('level_number');
        $query = $this->db->get();
        $data = $query->result_array();
        if (!empty($data)) {
            foreach ($data as $level) {
                $levels["level_".$level['level_number']] = $level['number'];
            }
            $levels['section_id'] = $secid;
            $levels['section_name'] = $secname;
            return $levels;
        }
        else
            return;
    }

    public function getSections($userid,$isDeactivated) {
        $query = $this->db->get_where('section', array('user_id' => $userid, 'isDeactivated' => $isDeactivated));
        $data = $query->result_array();
        if (!empty($data)) {
            return $data;
        }
        else
            return;
    }

    public function getStudent($userid) {
        $this->db->select('sp.*,u.*,sn.section_name as section');
        $this->db->from('student_profile sp, user u,section sn');
        $this->db->where('u.user_id', $userid);
        $this->db->where('u.user_id = sp.user_id');
        $this->db->where('sn.section_id = sp.section_id');
        $query = $this->db->get();
        return $query->row_array();
    }

    public function updateStudent($id, $data) {
        $query = $this->db->where('user_id', $id);
        $query = $this->db->update('student_profile', $data);
        return $query;
    }

    public function getStudentCountperSection($userid) {
        $sections = $this->getSections($userid,'N');
        if(!empty($sections)){
        foreach ($sections as $section) {
            $this->db->select('count(*) as number,s.section_id,s.section_name');
            $this->db->from('student_profile sp,section s,user u');
            $this->db->where('sp.section_id', $section['section_id']);
            $this->db->where('sp.section_id = s.section_id');
            $this->db->where('s.isDeactivated', 'N');
            //$this->db->where('u.isDeleted','N');
            $this->db->where('sp.user_id = u.user_id');
            $query = $this->db->get();
            $data[] = $query->result_array();
        }

        return $data;
        }else return;

//        $this->db->select('count(*) as number,s.section_id,s.section_name');
//        $this->db->from('student_profile sp,section s,user u');
//        $this->db->where_in('sp.section_id', $sectionIds);
//        $this->db->where('sp.section_id = s.section_id');
//        $this->db->where('s.isDeactivated','N');
//        $this->db->where('u.isDeleted','N');
//        $this->db->where('sp.user_id = u.user_id');
//        $this->db->group_by('section_id');
//        $query = $this->db->get();
//        echo $this->db->last_query();
//        return $query->result_array();
    }
    

    public function getMyStudents($userid,$isDeleted) {
        $sections = $this->getSections($userid,'N');
        if(!empty($sections)){
        foreach ($sections as $section) {
            $sectionIds[] = $section['section_id'];
        }
        $this->db->select('u.username,sp.*,l.colorhex,sn.section_name');
        $this->db->from('student_profile sp,section sn,user u,level l');
        $this->db->where_in('sp.section_id', $sectionIds);
        $this->db->where('sp.section_id = sn.section_id');
        $this->db->where('u.user_id = sp.user_id');
        $this->db->where('sp.level_number = l.level_number');
        $this->db->where('u.isDeleted', $isDeleted);
        $this->db->order_by('sp.firstname','ASC');
        $query = $this->db->get();
        return $query->result_array();
    }else return;
    
        }

    public function updateUser($id, $data) {
        $query = $this->db->where('user_id', $id);
        $query = $this->db->update('user', $data);
        return $query;
    }

    public function updateModerator($id, $data) {
        $query = $this->db->where('user_id', $id);
        $query = $this->db->update('moderator_profile', $data);
        return $query;
    }

    public function addSection($data) {
        $this->db->insert('section', $data);
        return;
    }

    public function updateSection($section_id, $data) {
        $query = $this->db->where('section_id', $section_id);
        $query = $this->db->update('section', $data);
        return $query;
    }

    public function addUser($data) {
        $this->db->insert('user', $data);
        $user_id = $this->db->insert_id();
        return $user_id;
    }

    public function addStudent($data) {
        $this->db->insert('student_profile', $data);
        $user_id = $this->db->insert_id();
        return;
    }

    public function addStatus($data) {
        $this->db->insert('status', $data);
        return;
    }

}

?>
